{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# King Phisher Jupyter Notebook\n",
    "This notebook contains useful recipes for interactive analysis of the data\n",
    "collected by King Phisher. This uses a direct connection to the back end\n",
    "database to retrieve the information for analysis. Once the environment is setup\n",
    "the functionality provided by Jupyter notebooks can be leveraged to visualize\n",
    "arbitrary data including graphing it with matplotlib. Various examples are\n",
    "included to perform basic analysis.\n",
    "\n",
    "## Initial Setup\n",
    "The setup cell below needs to be run to setup the environment and connect to the\n",
    "database. It will prompt for the directory where King Phisher is installed\n",
    "to import the necessary modules. It will also prompt for an optional database\n",
    "connection string. If no connection string is specified, it will be determined\n",
    "by reading the server configuration file in the install directory.\n",
    "\n",
    "### Remote Server Connections\n",
    "A remote connection to a database can be opened via SSH using port forwarding.\n",
    "The following command will open and forward local port 5432 (the default\n",
    "PostgreSQL service port) to the same on the remote server:\n",
    "\n",
    "`ssh -f -N -L 5432:localhost:5432 king-phisher.lan`\n",
    "\n",
    "This notebook can then be run on a seperate host given that King Phisher is\n",
    "installed, and the necessary database connection can be made."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# run this to be prompted for the necessary information to configure the environment\n",
    "from ipywidgets import widgets\n",
    "from IPython import display\n",
    "\n",
    "import collections\n",
    "import os\n",
    "import sys\n",
    "import tabulate\n",
    "import yaml\n",
    "\n",
    "from sqlalchemy import and_\n",
    "from sqlalchemy import or_\n",
    "\n",
    "king_phisher_home = widgets.Text(value='/opt/king-phisher', width='350px')\n",
    "king_phisher_server_config = widgets.Text(value='server_config.yml', width='350px')\n",
    "db_connection = widgets.Text(placeholder='optional', width='350px')\n",
    "setup_button = widgets.Button(description='Setup', width='550px')\n",
    "results = widgets.HTML()\n",
    "\n",
    "db_manager = None\n",
    "db_models = None\n",
    "\n",
    "def setup_and_initialize(_):\n",
    "    global db_manager\n",
    "    global db_models\n",
    "    directory = os.path.expanduser(king_phisher_home.value)\n",
    "    if not os.path.isfile(os.path.join(directory, 'king_phisher', '__init__.py')):\n",
    "        results.value = 'Invalid King Phisher Install Directory'\n",
    "        return\n",
    "    sys.path.append(directory)\n",
    "    from king_phisher.server.database import manager as db_manager\n",
    "    from king_phisher.server.database import models as db_models\n",
    "\n",
    "    if db_connection.value:\n",
    "        db_manager.init_database(db_connection.value)\n",
    "    else:\n",
    "        with open(os.path.join(directory, king_phisher_server_config.value), 'r') as file_h:\n",
    "            server_config = yaml.load(file_h)\n",
    "        db_manager.init_database(server_config['server']['database'])\n",
    "    results.value = 'Successfully Initialized The Database'\n",
    "\n",
    "setup_button.on_click(setup_and_initialize)\n",
    "display.display(widgets.HBox([widgets.HTML(value='Install Directory', width='200px'), king_phisher_home]))\n",
    "display.display(widgets.HBox([widgets.HTML(value='Server Configuration File', width='200px'), king_phisher_server_config]))\n",
    "display.display(widgets.HBox([widgets.HTML(value='Database Connection String', width='200px'), db_connection]))\n",
    "display.display(setup_button)\n",
    "display.display(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# get campaigns from a specific industry for comparison\n",
    "session = db_manager.Session()\n",
    "industries = dict([(industry.name, industry.id) for industry in session.query(db_models.Industry)])\n",
    "\n",
    "industry = widgets.Dropdown(\n",
    "    options=[''] + list(industries.keys()),\n",
    "    description='Industry: ',\n",
    ")\n",
    "query = session.query(db_models.CampaignType)\n",
    "query = query.order_by(db_models.CampaignType.name)\n",
    "campaign_types = collections.OrderedDict()\n",
    "for ctype in query:\n",
    "    campaign_types[ctype.id] = widgets.Checkbox(description=ctype.name, value=True)\n",
    "\n",
    "session.close()\n",
    "\n",
    "html_display = widgets.HTML()\n",
    "def on_generate_button_clicked(_):\n",
    "    campaigns = []\n",
    "    campaign_type_ids = [type_id for (type_id, checkbox) in campaign_types.items() if checkbox.value]\n",
    "    if industry.value:\n",
    "        industry_id = industries[industry.value]\n",
    "    else:\n",
    "        industry_id = None\n",
    "    session = db_manager.Session()\n",
    "    query = session.query(db_models.Campaign)\n",
    "    query = query.filter(db_models.Campaign.campaign_type_id.in_(campaign_type_ids))\n",
    "    query = query.join(db_models.Campaign.company, aliased=True)\n",
    "    if industry_id is not None:\n",
    "        query = query.filter(db_models.Company.industry_id == industry_id)\n",
    "    table = []\n",
    "    all_messages = 0\n",
    "    all_visits = 0\n",
    "    all_credentials = 0\n",
    "    for campaign in query:\n",
    "        messages = len(campaign.messages)\n",
    "        all_messages += messages\n",
    "        visits = len(campaign.visits)\n",
    "        all_visits += visits\n",
    "        credentials = len(campaign.credentials)\n",
    "        all_credentials += credentials\n",
    "        table.append((\n",
    "            campaign.id,\n",
    "            campaign.name,\n",
    "            ('' if campaign.company is None else campaign.company.name),\n",
    "            campaign.created.strftime('%Y-%m-%d'),\n",
    "            campaign.user_id,\n",
    "            \"{0:,}\".format(messages),\n",
    "            \"{0:,}\".format(visits),\n",
    "            \"{0:,}\".format(credentials),\n",
    "            campaign.campaign_type.name if campaign.campaign_type else ''\n",
    "        ))\n",
    "    session.close()\n",
    "    html_display.value = \"<h3>Showing {0:,} Campaigns</h3>\".format(len(table))\n",
    "    table.append((\n",
    "        '',\n",
    "        'Grand Totals',\n",
    "        '',\n",
    "        '',\n",
    "        '',\n",
    "        \"{:,}\".format(all_messages),\n",
    "        \"{:,}\".format(all_visits),\n",
    "        \"{:,}\".format(all_credentials),\n",
    "        ''\n",
    "    ))\n",
    "    html_display.value += '<table class=\"table table-striped\">'\n",
    "    html_display.value += '\\n'.join(tabulate.tabulate(table, headers=('ID', 'Name', 'Company', 'Created', 'User', 'Messages', 'Visits', 'Creds', 'Type'), tablefmt='html').split('\\n')[1:-1])\n",
    "    html_display.value += '</table>'\n",
    "\n",
    "generate_button = widgets.Button(description='Generate Stats')\n",
    "generate_button.on_click(on_generate_button_clicked)\n",
    "\n",
    "display.display(industry)\n",
    "list(map(display.display, campaign_types.values()))\n",
    "display.display(generate_button)\n",
    "display.display(html_display)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# display information about campaigns\n",
    "session = db_manager.Session()\n",
    "campaigns = session.query(db_models.Campaign)\n",
    "campaigns = campaigns.order_by(db_models.Campaign.created)\n",
    "table = []\n",
    "for campaign in campaigns:\n",
    "    table.append((\n",
    "        campaign.id,\n",
    "        campaign.name,\n",
    "        campaign.created.strftime('%Y-%m-%d'),\n",
    "        campaign.user_id,\n",
    "        \"{0:,}\".format(len(campaign.messages)),\n",
    "        \"{0:,}\".format(len(campaign.visits)),\n",
    "        \"{0:,}\".format(len(campaign.credentials)),\n",
    "        campaign.campaign_type.name if campaign.campaign_type else ''\n",
    "    ))\n",
    "session.close()\n",
    "print(tabulate.tabulate(table, headers=('ID', 'Name', 'Created', 'User', 'Messages', 'Visits', 'Creds', 'Type')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# show information on the campaign type tag\n",
    "session = db_manager.Session()\n",
    "tags = session.query(db_models.CampaignType)\n",
    "tags = tags.order_by(db_models.CampaignType.id)\n",
    "table = []\n",
    "for tag in tags:\n",
    "    table.append((\n",
    "        tag.id,\n",
    "        tag.name,\n",
    "        tag.description,\n",
    "        \"{0:,}\".format(len(tag.campaigns))\n",
    "    ))\n",
    "session.close()\n",
    "print(tabulate.tabulate(table, headers=('ID', 'Name', 'Description', 'Campaigns')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# display a pie chart of the campaigns grouped by their tag\n",
    "%matplotlib inline\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "import collections\n",
    "\n",
    "session = db_manager.Session()\n",
    "tags = session.query(db_models.CampaignType)\n",
    "tags = tags.order_by(db_models.CampaignType.id)\n",
    "campaign_count = session.query(db_models.Campaign).count()\n",
    "campaign_types = collections.OrderedDict(\n",
    "    (tag.name, len(tag.campaigns) / campaign_count) for tag in tags\n",
    ")\n",
    "session.close()\n",
    "\n",
    "plt.figure(1, figsize=(10, 10))\n",
    "plt.pie(list(campaign_types.values()), labels=campaign_types.keys(), autopct='%1.1f%%')\n",
    "\n",
    "plt.title('Campaign Types')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# delete a campaign by its id\n",
    "session = db_manager.Session()\n",
    "campaign = session.query(db_models.Campaign).filter_by(id=1).first()\n",
    "print(campaign)\n",
    "session.delete(campaign)\n",
    "session.commit()\n",
    "session.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "celltoolbar": "Edit Metadata",
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
